package com.yuda.tools.downloadanswer.service.impl;

import com.aliyun.oss.OSS;
import com.aliyun.oss.model.GetObjectRequest;
import com.yuda.tools.downloadanswer.Oss.OssMap;
import com.yuda.tools.downloadanswer.dto.*;
import com.yuda.tools.downloadanswer.mapper.DownLoadAnswerMapper;
import com.yuda.tools.downloadanswer.service.DownLoadAnswerService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.io.File;
import java.sql.*;
import java.util.Date;
import java.util.*;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
import java.util.concurrent.TimeUnit;
import java.util.concurrent.atomic.AtomicInteger;
import java.util.stream.Collectors;

@Service
public class DownLoadAnswerServiceImpl implements DownLoadAnswerService {


    @Autowired
    private DownLoadAnswerMapper downLoadAnswerMapper;


    @Value("${check.answerDbPath}")
    private String answerDbPath;

    @Value("${check.answerDownLoadPath}")
    private String answerDownLoadPath;

    @Value(("${check.logDownLoadPath}"))
    private String logDownLoadPath;

    @Value("${check.oss_inner_out}")
    private String ossInnerOrOut;

    @Value("${check.table_schema}")
    private String currentSchema;

    int threadNum = 21;

    private final ExecutorService threadPool = Executors.newWorkStealingPool(threadNum);

    private final AtomicInteger atomicInteger = new AtomicInteger(0);

    private final AtomicInteger atomicIntegerLog = new AtomicInteger(0);

    private final AtomicInteger canRun = new AtomicInteger(0);

    private final AtomicInteger unAssemblyDataCanRun = new AtomicInteger(0);

    private String unAssemblyDataTip = "";

    private String currentDownLoad = "";

    private Integer totalSize = 1000000;
    private Integer totalLogSize = 1000000;


    @Override
    public String saveAnswer(int testSession, int subjectIndex) {

        File file = new File(answerDownLoadPath);
        if (!file.isDirectory()) {
            file.mkdirs();
        }

        currentDownLoad = testSession + "_" + subjectIndex;
        threadPool.execute(() -> {
            atomicInteger.set(0);
            canRun.set(1);
            List<StudentOssDto> list = downLoadAnswerMapper.listStudentsForDownLoad(testSession, subjectIndex);
            if (!list.isEmpty()) {
                totalSize = list.size();

                for (StudentOssDto studentOssDto : list) {
                    threadPool.execute(() -> {
                        System.out.println("正在处理第 -> " + atomicInteger.incrementAndGet());
                        try {
                            doDownLoadAnswer(studentOssDto);
                        } catch (Exception e) {
                            e.printStackTrace();
                        }
                    });
                }
                while (atomicInteger.get() < totalSize) {
                    try {
                        TimeUnit.SECONDS.sleep(1);
                    } catch (InterruptedException e) {
                        e.printStackTrace();
                    }
                }
                System.out.println("下载完成....");
                //验证下载的记录 2021年11月26日，暂时去除 以后开启
//                List<Integer> subjectIdList = this.getSubjectId(testSession, subjectIndex);
//                for (Integer subjectId : subjectIdList) {
//                    System.out.println("验证的科目是： " + subjectId);
//                    //根据subjectId获取到条数
//                    int subjectCount = this.numSubjectCount(subjectId);
//                    System.out.println("取出的 ques_count " + subjectCount);
//                    //效验数据
//                    int dataCount = this.hasDataFormalAnswer(subjectId);
//                    System.out.println("是否具有数据：数据数量 " + dataCount);
//                    if (dataCount == 0) {
//                        this.saveError(subjectId, -1);
//                    } else {//有数据，验证条数
//                        System.out.println("有数据，进行验证题目数 ..");
//                        Integer count = this.authenticationData(subjectId, subjectCount);
//                        if (count > 0) { //条数不对
//                            System.out.println("题目数不对...");
//                            //把subjectId 和数量 存到数据库
//                            this.saveError(subjectId, count);
//                        } else {//一切正常
//                            System.out.println("题目数对...");
//                            //设置此科目正常 根据subjectId设置
//                            this.updateDownLoadSuccess(subjectId);
//                            //判断是否可以更新状态
//                            //SELECT if(count(id)= (select count(id) from sparta_paper_subject_relation),'y','n') success_status  from sparta_paper_subject_relation where download_success = 0
//                            String res = this.checkCanBeUpdateRoadMap(subjectId);
//                            System.out.println("检查能否 执行 更新roadMap... " + res);
//                            if (res.equals("y")) {//更新roadMap
//                                this.updateDownLoadRoadMap();
//                            }
//                        }
//                    }
//                }
                //更新formal answer 表的准考证 暂时不更新准考 2021年11月26日，暂时去除 以后开启
//                this.updateZkz();
            }
            canRun.set(0);
        });
        return null;
    }

    @Override
    public String getDownLoadProgress() {
        return String.format("%.2f", atomicInteger.get() * 1.0 / this.totalSize);
    }

    @Override
    public boolean checkCanRun() {
        return canRun.get() == 0;
    }

    @Override
    public List<PaperSubject> downLoadAnswerList() {

        List<PaperSubject> list = downLoadAnswerMapper.downLodAnswerList();
        List<DownLoadProcess> downLoadStatus = downLoadAnswerMapper.findDownLoadStatus();
        Map<String, DownLoadProcess> collect = downLoadStatus.stream().collect(Collectors.toMap(DownLoadProcess::getKey, x -> x));

        for (PaperSubject paperSubject : list) {
            //获取是每一场考试下载的进度

            DownLoadProcess downLoadProcess = collect.get(paperSubject.getTestSession() + "_" + paperSubject.getSubjectIndex());
            if (downLoadProcess == null) continue;
            Integer needCheck = downLoadProcess.getNeedCheck();//需要检查的数量
            Integer checkedNum = downLoadProcess.getCheckedNum();
            Integer downLoadNum = downLoadProcess.getDownLoadNum();
            if (checkedNum == 0) {//还没开始检查
                paperSubject.setStatus("检查未开始");
            } else if (downLoadNum.equals(checkedNum) && downLoadNum.equals(needCheck)) { //下载的人数 = 检查的人数 = 需要检查的人数
                paperSubject.setStatus("下载成功");
            } else if (checkedNum >= needCheck && downLoadNum == 0) { //检查的人数 等于 检查的人数  并且 没有被下载
                paperSubject.setStatus("检查已完成可以下载");
            } else if (atomicInteger.get() > 0
                    && downLoadNum < checkedNum
                    && currentDownLoad.equals(paperSubject.getTestSession() + "_" + paperSubject.getSubjectIndex())) { //下载人数小于 需要下载的人数 并在在执行下载
                paperSubject.setStatus("下载中");
                paperSubject.setProcessBar(String.format("%.2f", atomicInteger.get() * 1.0 / this.totalSize));
            } else if (atomicInteger.get() == 0 && downLoadNum < checkedNum) {
                paperSubject.setStatus("下载失败");
            } else if (checkedNum < needCheck) {
                paperSubject.setStatus("检查未完成");
            }
        }
        return list;
    }

    /**
     * 给页面提供数据（formal answer -1  mq 非-1 列表）
     * 思路：  先去重，在关联去重的表，获取数据。无表则创建，有表则不执行。除非去重表没有数据才执行，且只执行一次
     */
    @Override
    public List<TroubleMaker> unAssemblyData() {

        if (unAssemblyDataCanRun.get() == 0) { //判断此代码是否可以被执行
            try {
                unAssemblyDataCanRun.set(1); //设置不可以执行

                //查询是否具备执行去重的条件 条件是没有mq_answer_qc表 或者有mq_answer_qc表，但是此表中没有记录
                int count = downLoadAnswerMapper.determineCanBeExecuted(currentSchema);
                if (count == 0) { //可以执行 但是需要创建表
                    unAssemblyDataTip = "正在创建mq_answer_qc表";
                    createTableNamedMqQc();//创建mq 去重表
                }

                if (count > 1) { //判断mq_qc表中有没有 记录
                    int totalRows = downLoadAnswerMapper.haveAnyRecord();
                    if (totalRows == 0) {
                        //没有记录，则进行去重插入操作 此过程 424 测试库 花费大概 80秒
                        // 但是必须等待，因为页面还等着返回数据呢，不可以开线程去单独执行
                        unAssemblyDataTip = "正在去重";
                        downLoadAnswerMapper.removeMqDuplicates();
                    }
                }
                unAssemblyDataTip = "开始查询";
                //查询结果
                return downLoadAnswerMapper.listUnAssemblyData();

            } finally {
                unAssemblyDataCanRun.set(0);
            }
        }
        return Collections.singletonList(new TroubleMaker());
    }

    @Override
    public void createTableNamedMqQc() {
        downLoadAnswerMapper.doCreateTableMqQc();
    }

    //判断mq  去重表是否有记录
    @Override
    public int hasAnyRecords() {
        return downLoadAnswerMapper.haveAnyRecord();
    }

    @Override
    public void executeCleanRedundantMq() {
        downLoadAnswerMapper.removeMqDuplicates();
    }

    @Override
    public int cleanUpOldFormalAnswerData() {
        return downLoadAnswerMapper.cleanUpOldFormalAnswerData();
    }

    @Override
    public List<TroubleMaker> listUnAssemblyData() {
        return downLoadAnswerMapper.listUnAssemblyData();
    }

    @Override
    public int updateAnswerFromMq() {
        return downLoadAnswerMapper.updateAnswerFromMq();
    }


    @Override
    public int getPcNoByStudentId(String studentId) {

        return downLoadAnswerMapper.getPcNoByStudentId(studentId);
    }

    @Override
    public int truncateMqQc() {
        return downLoadAnswerMapper.truncateMqQc();
    }

    @Override
    public int downLoadLog(Integer testSession) {

        File file = new File(logDownLoadPath);
        if (!file.isDirectory()) {
            file.mkdir();
        }
        atomicIntegerLog.set(0);

        int paperId = downLoadAnswerMapper.getPaperIdByTestSession(testSession);
//        downLoadAnswerMapper.deleteByPaperId(paperId);
        //判断日志表有没有数据 如果有 直接返回
//        int totalSize = downLoadAnswerMapper.checkCanBeExecute(paperId);
//        if (totalSize > 0) {
//            return -1;
//        }
        List<StudentLog> list = downLoadAnswerMapper.listStudentsLogForDownLoad(paperId);
        if (!list.isEmpty()) {
            totalLogSize = list.size();

            for (StudentLog studentLog : list) {
                threadPool.execute(() -> {
                    System.out.println("正在处理第 -> " + atomicIntegerLog.incrementAndGet());
                    try {
                        doDownLoadLog(paperId, studentLog);
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                });
            }
            while (atomicIntegerLog.get() < totalLogSize) {
                try {
                    TimeUnit.SECONDS.sleep(1);
                } catch (InterruptedException e) {
                    e.printStackTrace();
                }
            }
        }
        return 0;
    }

    @Override
    public String logProgress() {
        return String.format("%.2f", atomicIntegerLog.get() * 1.0 / this.totalLogSize);
    }

    @Override
    public List<Integer> getSubjectId(int testSession, int subjectIndex) {
        return downLoadAnswerMapper.getSubjectId(testSession, subjectIndex);
    }

    @Override
    public int numSubjectCount(int subjectId) {
        return downLoadAnswerMapper.numSubjectCount(subjectId);
    }

    @Override
    public Integer authenticationData(int subjectId, int subjectCount) {
        return downLoadAnswerMapper.authenticationData(subjectId, subjectCount);
    }

    @Override
    public void saveError(int subjectId, int subjectCount) {
        downLoadAnswerMapper.saveError(subjectId, subjectCount, new Date());
    }

    @Override
    public int hasDataFormalAnswer(Integer subjectId) {
        return downLoadAnswerMapper.hasDataFormalAnswer(subjectId);
    }

    @Override
    public void updateDownLoadSuccess(Integer subjectId) {
        downLoadAnswerMapper.updateDownLoadSuccess(subjectId);
    }

    @Override
    public String checkCanBeUpdateRoadMap(Integer subjectId) {
        return downLoadAnswerMapper.checkCanBeUpdateRoadMap(subjectId);
    }

    @Override
    public void updateDownLoadRoadMap() {
        downLoadAnswerMapper.updateDownLoadRoadMap();
    }

    @Override
    public void updateMergeRoadMap() {
        downLoadAnswerMapper.updateMergeRoadMap();
    }

    @Override
    public void updateZkz() {
        downLoadAnswerMapper.updateZkz();
    }

    private void doDownLoadLog(int paperId, StudentLog studentLog) {
        String studentId = studentLog.getStudentId();
        String realArea = studentLog.getRealArea();
        Integer pcNo = studentLog.getPcNo();
        OSS oss = OssMap.get(realArea + ossInnerOrOut.trim());
        String fileName = answerDbPath + studentId + "_" + paperId + "_" + studentLog.getPcNo() + "_stat.db";
        String downLoadFileName = logDownLoadPath + "/" + studentId + "_" + paperId + "_" + studentLog.getPcNo() + "_stat.db";
        if (!oss.doesObjectExist(studentLog.getRealArea(), fileName)) {
            System.out.println("文件不存在....");
            return;
        }
        oss.getObject(new GetObjectRequest(studentLog.getRealArea(), fileName), new File(downLoadFileName));

        Connection c = null;
        PreparedStatement st = null;
        ResultSet rs = null;
        try {
            Class.forName("org.sqlite.JDBC");
            c = DriverManager.getConnection("jdbc:sqlite:" + downLoadFileName);
            c.setAutoCommit(false);
            st = c.prepareStatement("select id,student_id,paper_id,subject_id,subject_relation_id,ques_id,answer,answer_abc,md5,msg,stat_type,code,`time`  from stat");
            rs = st.executeQuery();
            List<LogContext> list = new ArrayList<>();
            while (rs.next()) {
                LogContext logContext = new LogContext();
                logContext.setLogId(rs.getInt("id"));
                logContext.setStudentId(studentId);
                logContext.setPaperId(rs.getInt("paper_id"));
                logContext.setSubjectId(rs.getInt("subject_id"));
                logContext.setSubjectRelationId(rs.getInt("subject_relation_id"));
                logContext.setQuesId(rs.getString("ques_id"));
                //TODO
                logContext.setAnswer(rs.getString("answer"));
                logContext.setAnswerAbc(rs.getString("answer_abc"));
                logContext.setMd5(rs.getString("md5"));
                logContext.setMessage(rs.getString("msg"));
                logContext.setStatType(rs.getInt("stat_type"));
                logContext.setCode(rs.getInt("code"));
                logContext.setTime(rs.getString("time"));
                logContext.setBucketName(realArea);
                logContext.setPcNo(pcNo);
                list.add(logContext);
            }

            downLoadAnswerMapper.batchSaveLog(list);
            if (studentLog.getPcStatus().equals("1")) {
                //更新sparta exam student
                downLoadAnswerMapper.updateExamStudentStatusForLog(studentLog.getId());
            } else if (studentLog.getPcStatus().equals("2")) {
                //更新change表
                downLoadAnswerMapper.updateChangeTableStatusForLog(studentLog.getStudentId(), pcNo);
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }

                if (st != null) {
                    st.close();
                }

                if (c != null) {
                    c.close();
                }

                File file = new File(fileName);
                if (file.exists()) file.delete();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }


    private void doDownLoadAnswer(StudentOssDto studentOssDto) {

        OSS oss = OssMap.get(studentOssDto.getRealArea() + ossInnerOrOut.trim());
        oss.getObject(new GetObjectRequest(studentOssDto.getRealArea(), answerDbPath + studentOssDto.getRealFileName()), new File(answerDownLoadPath + "/" + studentOssDto.getRealFileName()));

        Connection c = null;
        PreparedStatement st = null;
        ResultSet rs = null;
        try {
            Class.forName("org.sqlite.JDBC");
            c = DriverManager.getConnection("jdbc:sqlite:" + answerDownLoadPath + "/" + studentOssDto.getRealFileName());
            c.setAutoCommit(false);
            st = c.prepareStatement("select studentid,quesid,subjectid,subject_relation_id, paperid,answer,answer_abc,create_time,update_time,md5,type from answer");
            rs = st.executeQuery();
            List<StudentAnswer> list = new ArrayList<>();

            while (rs.next()) {
                StudentAnswer studentAnswer = new StudentAnswer();
                studentAnswer.setStudentId(rs.getString("studentid"));
                studentAnswer.setSubjectId(rs.getInt("subjectid"));
                studentAnswer.setSubjectRelationId(rs.getInt("subject_relation_id"));
                studentAnswer.setQuesId(rs.getInt("quesid"));
                studentAnswer.setPaperId(rs.getInt("paperid"));
                studentAnswer.setAnswerAbc(rs.getString("answer_abc"));
                studentAnswer.setAnswer(rs.getString("answer"));//
                studentAnswer.setMd5(rs.getString("md5"));
                studentAnswer.setType(rs.getInt("type"));
                studentAnswer.setCreateTime(rs.getString("create_time"));
                studentAnswer.setUpdateTime(rs.getString("update_time"));
                list.add(studentAnswer);
            }
            downLoadAnswerMapper.batchSaveAnswer(list);
            //2021 11 26 暂时注释 提升效率
            downLoadAnswerMapper.updateStatus(studentOssDto.getSessId());
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }

                if (st != null) {
                    st.close();
                }

                if (c != null) {
                    c.close();
                }

                File file = new File(answerDownLoadPath + "/" + studentOssDto.getRealFileName());
                file.delete();

            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

    }

    public String getUnAssemblyDataTip() {
        return unAssemblyDataTip;
    }

    @Transactional
    @Override
    public void unionFormalAnswer() {
        downLoadAnswerMapper.cleanUpOldFormalAnswerData();
        downLoadAnswerMapper.unionFormalAnswer();
        //更新准考证
        this.updateZkz();
    }

    @Override
    public int determineWhetherTableNeedsToBeCreated() {

        return downLoadAnswerMapper.determineCanBeExecuted(currentSchema);
    }
}
